﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class Operation 
    {
        public static Guid CreateOperation(DTO.Operation operation, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            Guid policyID = Guid.Empty;
            //Guid implementationID = Guid.Empty;
            const string sprocname = "[ServiceEngine].[CreateOperation]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter FQNParam = new SqlParameter("@FQN", SqlDbType.NVarChar, 512);
                    SqlParameter RESTVerbParam = new SqlParameter("@RESTVerb", SqlDbType.TinyInt);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = operation.Name;
                    DescriptionParam.Value = operation.Description;

                    if (operation.Policy != null)
                    {
                        PolicyIDParam.Value = operation.Policy.ID;
                    }
                    else
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }

                    if (string.IsNullOrEmpty(operation.FQN) == true)
                    {
                        FQNParam.Value = DBNull.Value;
                    }
                    else
                    {
                        FQNParam.Value = operation.FQN;
                    }

                    RESTVerbParam.Value = operation.RESTVerb;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(FQNParam);
                    cmd.Parameters.Add(RESTVerbParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;

                    operation.ID = ID;
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static DTO.Operation UpdateOperation(DTO.Operation operation, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateOperation]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter PolicyIDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    SqlParameter FQNParam = new SqlParameter("@FQN", SqlDbType.NVarChar, 512);
                    SqlParameter RESTVerbParam = new SqlParameter("@RESTVerb", SqlDbType.TinyInt);

                    cmd.CommandType = CommandType.StoredProcedure;

                    IDParam.Value = operation.ID;
                    NameParam.Value = operation.Name;
                    DescriptionParam.Value = operation.Description;

                    if (operation.Policy == null)
                    {
                        PolicyIDParam.Value = DBNull.Value;
                    }
                    else
                    {
                        PolicyIDParam.Value = operation.Policy.ID;
                    }
                    if (string.IsNullOrEmpty(operation.FQN) == true)
                    {
                        FQNParam.Value = DBNull.Value;
                    }
                    else
                    {
                        FQNParam.Value = operation.FQN;
                    }

                    RESTVerbParam.Value = operation.RESTVerb;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(PolicyIDParam);
                    cmd.Parameters.Add(FQNParam);
                    cmd.Parameters.Add(RESTVerbParam);

                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;

                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return operation;
        }

        public static void DeleteOperation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteOperation]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }


                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;

                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static DTO.Operation GetOperation(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.Operation operation = new DTO.Operation();
            const string sprocname = "[ServiceEngine].[GetOperation]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            operation.ID = reader.GetGuid(0);
                            operation.Name = reader.GetString(1);
                            if (reader.IsDBNull(2))
                            {
                                operation.Description = null;
                            }
                            else
                            {
                                operation.Description = reader.GetString(2);
                            }
                            if (reader.IsDBNull(3))
                            {
                                operation.Policy = null;
                            }
                            else
                            {
                                operation.Policy = CatalogSvcHelper.AssemblePolicyFromPolicyAssertion(reader.GetGuid(3), txn, conn);
                            }
                            if (reader.IsDBNull(4))
                            {
                                operation.FQN = null;
                            }
                            else
                            {
                                operation.FQN = reader.GetString(4);
                            }
                            if (reader.IsDBNull(5))
                            {
                                operation.RESTVerb = DTO.RESTVerbType.None;
                            }
                            else
                            {
                                operation.RESTVerb = (DTO.RESTVerbType)reader.GetByte(5);
                            }
                        }
                    }
                }
                //DTO.OperationVersionList list = OperationVersion.ListOperationVersions(ID, txn, conn);

                //if (list.OperationVersions != null)
                //{
                //    operation.Versions = new List<DTO.OperationVersion>();
                //    foreach (DTO.OperationVersionListElement version in list.OperationVersions)
                //    {
                //        operation.Versions.Add(OperationVersion.GetOperationVersion(version.ID, txn, conn));
                //    }
                //}
                //operation.EndpointList = Endpoint.ListAssociatedEndpointsForOperation(ID, txn, conn);

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return operation;

        }

        public static DTO.OperationList ListOperations(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationList list = new DTO.OperationList();
            const string sprocname = "[ServiceEngine].[ListOperations]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<DTO.OperationListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationListElement element = new DTO.OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.OperationList ListOperationsByElement(Guid SchemaID, string elementName, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationList list = new DTO.OperationList();
            const string sprocname = "[ServiceEngine].[ListOperationsByElement]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@ElementName", SqlDbType.NVarChar, 400);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = SchemaID;
                    cmd.Parameters.Add(IDParam);
                    NameParam.Value = elementName;
                    cmd.Parameters.Add(NameParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<DTO.OperationListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationListElement element = new DTO.OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.OperationList ListOperationsBySchema(Guid SchemaID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationList list = new DTO.OperationList();
            const string sprocname = "[ServiceEngine].[ListOperationsBySchema]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = SchemaID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<DTO.OperationListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationListElement element = new DTO.OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.OperationList ListOperationsByResource(Guid ResourceID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationList list = new DTO.OperationList();
            const string sprocname = "[ServiceEngine].[ListOperationsByResource]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ResourceID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ResourceID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<DTO.OperationListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationListElement element = new DTO.OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.OperationList ListOperationsByFirstElement(Guid SchemaID, string elementName, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationList list = new DTO.OperationList();
            const string sprocname = "[ServiceEngine].[ListOperationsByFirstElement]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@SchemaID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@ElementName", SqlDbType.NVarChar, 400);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = SchemaID;
                    cmd.Parameters.Add(IDParam);
                    NameParam.Value = elementName;
                    cmd.Parameters.Add(NameParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<DTO.OperationListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationListElement element = new DTO.OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.OperationListElement> ListOperationsByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.OperationListElement> list = new List<DTO.OperationListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.OperationListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationListElement element = new DTO.OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.EntityGroupList ListOperationGroupsByOperation(Guid entityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EntityGroupList list = new DTO.EntityGroupList();
            const string sprocname = "[ServiceEngine].[ListEntityGroupsByEntity]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityID", SqlDbType.UniqueIdentifier);
                    IDParam.Value = entityID;
                    cmd.Parameters.Add(IDParam);

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.EntityGroups = new List<DTO.EntityGroupListElement>();
                            while (reader.Read())
                            {
                                DTO.EntityGroupListElement element = new DTO.EntityGroupListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.EntityGroups.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;
        }

        public static DTO.EntityGroupList ListOperationGroups(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.EntityGroupList list = new DTO.EntityGroupList();
            const string sprocname = "[ServiceEngine].[ListEntityGroups]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityTypeID", SqlDbType.SmallInt);
                    IDParam.Value = 4;
                    cmd.Parameters.Add(IDParam);

                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.EntityGroups = new List<DTO.EntityGroupListElement>();
                            while (reader.Read())
                            {
                                DTO.EntityGroupListElement element = new DTO.EntityGroupListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.EntityGroups.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;
        }
        public static Guid GetIDForOperation(string name, string fqn, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[GetIDForOperation]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter FQNParam = new SqlParameter("@FQN", SqlDbType.NVarChar,512);
                    SqlParameter IDParam = new SqlParameter("@PolicyID", SqlDbType.UniqueIdentifier);
                    IDParam.Direction = ParameterDirection.Output;


                    cmd.CommandType = CommandType.StoredProcedure;

                    if (string.IsNullOrEmpty(fqn) == true)
                        FQNParam.Value = DBNull.Value ;
                    else
                        FQNParam.Value = fqn;

                    NameParam.Value = name;

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(FQNParam);

                    cmd.ExecuteNonQuery();
                    if (IDParam.Value != DBNull.Value)
                        ID = (Guid)IDParam.Value;
                    else
                        ID = Guid.Empty;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }
    }
}
